package com.ph.irp.base.assistantmaterial.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialSuppliercategoryDao;

import com.ph.irp.model.PBaseAssistantmaterialSuppliercategory;
import com.ph.util.DBUtil;
/**
 * 辅助资料-供应商分类
 * @author wgb
 *
 */
public class PBaseAssistantmaterialSuppliercategoryDaoImpl implements
		PBaseAssistantmaterialSuppliercategoryDao {
	private ComboPooledDataSource dataSource;
	private List<PBaseAssistantmaterialSuppliercategory> suppliercategoryList = null;
	private PBaseAssistantmaterialSuppliercategory suppliercategoryModel;
    /**
     * 增加辅助资料-行业信息
     */
	@Override
	public void addSuppliercategory(
			PBaseAssistantmaterialSuppliercategory suppliercatagoryModel)throws Throwable {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sb = new StringBuilder();
		sb.append("insert into p_base_assistantmaterial_suppliercategory(SupplierCategoryCode,SupplierCategoryName) values(?,?)");
		try{
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, suppliercatagoryModel.getSupplierCategoryCode().trim());
			preparedStatement.setString(2, suppliercatagoryModel.getSupplierCategoryName().trim());
			preparedStatement.execute();
		}
      catch(Exception e){
    	  throw new Exception(e.getMessage());
      }
       finally{
    	   try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {

				throw new Exception(ex.getMessage());
			}
       }
	}

	/**
	 * 删除辅助资料-行业信息
	 */
	public void deleteSuppliercategoryById(Object[] supplierCategoryIds) throws Throwable {
		int loop = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		// ResultSet resultSet = null;
		
		//StringBuilder sb = new StringBuilder();
		//sb.append("delete from p_base_assistantmaterial_industry where IndustryId=?");
		try {
			connection = this.dataSource.getConnection();
			for (loop = 0; loop < supplierCategoryIds.length; loop++){
						preparedStatement = connection.prepareStatement("delete from p_base_assistantmaterial_suppliercategory where SupplierCategoryID=?");
						int supplierCategoryId = Integer.valueOf((String) supplierCategoryIds[loop]);
						preparedStatement.setInt(1, supplierCategoryId);
						preparedStatement.execute();
			  }
		} 
		catch(Exception e){
			throw new Exception(e.getMessage());
		}

		finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}

		}

	}
	/**
	 * 查询所有的辅助资料-行业信息
	 */
	@Override
	public List<PBaseAssistantmaterialSuppliercategory> getALLSuppliercategory() throws Throwable{

		suppliercategoryList = new ArrayList<PBaseAssistantmaterialSuppliercategory>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select SupplierCategoryID,SupplierCategoryCode,SupplierCategoryName from p_base_assistantmaterial_suppliercategory");
		try{
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(suppliercategoryList, resultSet);
			
		}
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally{
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return suppliercategoryList;
	}
	
	/**
	 * 根据代码查找辅助资料-行业信息
	 */
	@Override
	public List<PBaseAssistantmaterialSuppliercategory> getSuppliercategoryByCode(
			String supplierCategoryCode) throws Throwable{

		suppliercategoryList = new ArrayList<PBaseAssistantmaterialSuppliercategory>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select SupplierCategoryID,SupplierCategoryCode,SupplierCategoryName from p_base_assistantmaterial_suppliercategory where SupplierCategoryCode like ?");
		try{
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, "%"+supplierCategoryCode+"%");
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(suppliercategoryList, resultSet);
			
		}
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally{
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return suppliercategoryList;
	}
	 /**
	  * 根据代码和名称查找辅助资料-行业信息
	  */
	@Override
	public List<PBaseAssistantmaterialSuppliercategory> getSuppliercategoryByCodeAndName(
			String supplierCategoryCode, String supplierCategoryName) throws Throwable{

		suppliercategoryList = new ArrayList<PBaseAssistantmaterialSuppliercategory>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select SupplierCategoryID,SupplierCategoryCode,SupplierCategoryName from p_base_assistantmaterial_suppliercategory where SupplierCategoryCode like ? and  SupplierCategoryName like ?");
		try{
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, "%"+supplierCategoryCode+"%");
			preparedStatement.setString(2, "%"+supplierCategoryName+"%");
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(suppliercategoryList, resultSet);
			
		}
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally{
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return suppliercategoryList;
	}
	/**
	 * 根据id查找辅助资料-行业信息
	 */
	@Override
	public PBaseAssistantmaterialSuppliercategory getSuppliercategoryById(
			int supplierCategoryId) throws Throwable{
		suppliercategoryList = new ArrayList<PBaseAssistantmaterialSuppliercategory>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select SupplierCategoryID,SupplierCategoryCode,SupplierCategoryName from p_base_assistantmaterial_suppliercategory where SupplierCategoryID=?");
		try{
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, supplierCategoryId);
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(suppliercategoryList, resultSet);
			suppliercategoryModel = suppliercategoryList.get(0);
			
		}
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally{
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return suppliercategoryModel;
	}

	@Override
	public List<PBaseAssistantmaterialSuppliercategory> getSuppliercategoryByName (
			String supplierCategoryName)throws Throwable {
		suppliercategoryList = new ArrayList<PBaseAssistantmaterialSuppliercategory>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select SupplierCategoryID,SupplierCategoryCode,SupplierCategoryName from p_base_assistantmaterial_suppliercategory where SupplierCategoryName like ?");
		try{
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, "%"+supplierCategoryName+"%");
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(suppliercategoryList, resultSet);
			
		}
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally{
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
			throw new Exception(ex.getMessage());
			}
		}
		return suppliercategoryList;
	}

	@Override
	public void updateSuppliercategory(
			PBaseAssistantmaterialSuppliercategory suppliercatagoryModel)throws Throwable {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_assistantmaterial_suppliercategory set SupplierCategoryCode=?,SupplierCategoryName=? where SupplierCategoryID=?");
		try{
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, suppliercatagoryModel.getSupplierCategoryCode());
			preparedStatement.setString(2, suppliercatagoryModel.getSupplierCategoryName());
			preparedStatement.setInt(3,suppliercatagoryModel.getSupplierCategoryId());
			preparedStatement.execute();
		}
     catch(Exception e){
    	 throw new Exception(e.getMessage());
     }
       finally{
    	   try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {

			throw new Exception(ex.getMessage());
			}
       }
	}
	/**
	 * 查看是否存在相同的代码
	 * @param supplierCategoryCode
	 * @return
	 */
	public boolean isSameCode(String supplierCategoryCode)throws Throwable{
		
		suppliercategoryList = new ArrayList<PBaseAssistantmaterialSuppliercategory>();
		Connection connection = null;
		ResultSet resultSet = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select SupplierCategoryID,SupplierCategoryCode,SupplierCategoryName from p_base_assistantmaterial_suppliercategory where SupplierCategoryCode = ?");
		try{
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, supplierCategoryCode);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				return true;
			}
			else{
				return false;
			}
			
		}
		catch(Exception e){
		
			throw new Exception(e.getMessage());
		}
		finally{
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
			throw new Exception(ex.getMessage());
			}
		}
		
	}
	/**
	 * 查看是否存在相同的名称
	 * @param supplierCategoryName
	 * @return
	 */
	public boolean isSameName(String supplierCategoryName)throws Throwable {
		
		suppliercategoryList = new ArrayList<PBaseAssistantmaterialSuppliercategory>();
		Connection connection = null;
		ResultSet resultSet = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select SupplierCategoryID,SupplierCategoryCode,SupplierCategoryName from p_base_assistantmaterial_suppliercategory where SupplierCategoryName = ?");
		try{
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, supplierCategoryName);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				return true;
			}
			else{
				return false;
			}
			
		}
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally{
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
			throw new Exception(ex.getMessage());
			}
		}
		
	}

	/**
	 * @return the suppliercategoryList
	 */
	public List<PBaseAssistantmaterialSuppliercategory> getSuppliercategoryList() {
		return suppliercategoryList;
	}

	/**
	 * @param suppliercategoryList the suppliercategoryList to set
	 */
	public void setSuppliercategoryList(
			List<PBaseAssistantmaterialSuppliercategory> suppliercategoryList) {
		this.suppliercategoryList = suppliercategoryList;
	}

	/**
	 * @return the suppliercategoryModel
	 */
	public PBaseAssistantmaterialSuppliercategory getSuppliercategoryModel() {
		return suppliercategoryModel;
	}

	/**
	 * @param suppliercategoryModel the suppliercategoryModel to set
	 */
	public void setSuppliercategoryModel(
			PBaseAssistantmaterialSuppliercategory suppliercategoryModel) {
		this.suppliercategoryModel = suppliercategoryModel;
	}

	/**
	 * @return the dataSource
	 */
	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	/**
	 * @param dataSource the dataSource to set
	 */
	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}
	/**
	 * 将resultset转换成List函数
	 * @param suppliercategoryList
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 */
	public List<PBaseAssistantmaterialSuppliercategory> turnResultSetToList(
			List<PBaseAssistantmaterialSuppliercategory> suppliercategoryList,
			ResultSet resultSet) throws Throwable {

		while (resultSet.next()) {

			PBaseAssistantmaterialSuppliercategory assistantmaterialSuppliercategory = new PBaseAssistantmaterialSuppliercategory();

			assistantmaterialSuppliercategory.setSupplierCategoryId(resultSet.getInt("SupplierCategoryID"));
			assistantmaterialSuppliercategory.setSupplierCategoryCode(resultSet.getString("SupplierCategoryCode"));
			assistantmaterialSuppliercategory.setSupplierCategoryName(resultSet.getString("SupplierCategoryName"));
			suppliercategoryList.add(assistantmaterialSuppliercategory);

		}
		return suppliercategoryList;
	}
}
